﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spInitializeDatabaseConfiguration]
#-- Purpose:		Sets the default configuration options
#--	Last Update:	05/15/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	msdb.dbo.sysmail_add_account_sp
#--					msdb.dbo.sysmail_add_profile_sp
#--					msdb.dbo.sysmail_add_profileaccount_sp
#--					msdb.dbo.sysmail_add_principalprofile_sp
#--					msdb.dbo.sp_send_dbmail
#--					msdb.dbo.sp_set_sqlagent_properties
#--					master.dbo.xp_instance_regwrite
#--					msdb.dbo.sp_add_operator
#--					msdb.dbo.sp_add_alert
#--					msdb.dbo.sp_update_alert
#--					msdb.dbo.sp_add_notification
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spInitializeDatabaseConfiguration]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@engine int, @major int, @minor int
DECLARE @iValue int, @sValue varchar(255), @bChange bit
DECLARE	@SMTPServer varchar(100), @sqlAdminName nvarchar(30), @email_address nvarchar(100),
		@testmsg varchar(100), @sCommand nvarchar(MAX)

--- Determine the type/version of SQL Server installed
SELECT		@engine = CAST(SERVERPROPERTY('EngineEdition') AS int), -- 3 = Ent/Dev, 2 = Std
			@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int),
			@minor = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),3) AS int)
			
--- Configure Server Settings
IF @major >= 9
  BEGIN
	EXEC sp_executesql N'sp_configure ''show advanced options'', 1; RECONFIGURE WITH OVERRIDE;'
	EXEC sp_executesql N'sp_configure ''clr enabled'', 1; RECONFIGURE WITH OVERRIDE;'
	EXEC sp_executesql N'sp_configure ''Database Mail XPs'', 1; RECONFIGURE WITH OVERRIDE;'
	EXEC sp_executesql N'sp_configure ''Agent XPs'', 1; RECONFIGURE WITH OVERRIDE;'
	IF (@major >= 10 AND @minor >= 50 AND @engine IN (2, 3)) OR (@major >= 10 AND @minor < 50 AND @engine = 3)
		EXEC sp_executesql N'sp_configure ''backup compression default'', 1; RECONFIGURE WITH OVERRIDE;'
	EXEC sp_executesql N'sp_configure ''show advanced options'', 0; RECONFIGURE WITH OVERRIDE;'
  END
  
/*  Enable the SMTP configuration.  */
SELECT	@SMTPServer = '$(AdminSmtpServer)',
		@sqlAdminName = '$(AdminEmailAccount)',
		@email_address = @sqlAdminName + '@$(AdminEmailDomainName)',
		@testmsg = 'Test from ' + ISNULL(@@servername, CAST(SERVERPROPERTY('ServerName') as varchar(255))),
		@bChange = 0

--Create database mail account.
IF NOT EXISTS (SELECT TOP 1 * FROM msdb.dbo.sysmail_account WHERE name = 'sqlservice')
  BEGIN
	exec msdb.dbo.sysmail_add_account_sp
			@Account_name = 'sqlservice',
			@description = 'SQL Server Service Account',
			@email_address = @email_address,
			@replyto_address = @email_address,
			@display_name = 'SQL Server Service Account',
			@mailserver_name = @SMTPServer

	SET	@bChange = 1
	PRINT '   Added the mail account [sqlservice]'
  END

--Create global mail profile.
IF NOT EXISTS (SELECT TOP 1 * FROM msdb.dbo.sysmail_profile WHERE name = 'SqlSmtp')
  BEGIN
	exec msdb.dbo.sysmail_add_profile_sp
			@profile_name = 'SqlSmtp',
			@description = 'Mail profile setup for email from this SQL Server'

	SET	@bChange = 1
	PRINT '   Added the mail profile [SqlSmtp]'
  END

--Add the account to the profile.
IF NOT EXISTS (	SELECT	TOP 1 *
				FROM	msdb.dbo.sysmail_profileaccount pa
				JOIN	msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
				JOIN	msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
				WHERE	p.name = 'SqlSmtp'
						AND a.name = 'sqlservice')
  BEGIN
	exec msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name = 'SqlSmtp',
			@Account_name = 'sqlservice',
			@sequence_number=1

	SET	@bChange = 1
	PRINT '   Attached the mail account [sqlservice] to the mail profile [SqlSmtp]'
  END

--grant access to the profile to all users in the msdb database
IF NOT EXISTS (	SELECT	TOP 1 * 
				FROM	msdb.dbo.sysmail_principalprofile pp
				JOIN	msdb.dbo.sysmail_profile p ON pp.profile_id = p.profile_id
				WHERE	p.name = 'SqlSmtp')
  BEGIN
	exec msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name = 'SqlSmtp',
			@principal_name = 'public',
			@is_default = 1

	SET	@bChange = 1
	PRINT '   Set the mail profile [SqlSmtp] as the default public mail profile'
  END

/*  Send a test message.  */
IF @bChange = 1
  BEGIN
	exec msdb.dbo.sp_send_dbmail @profile_name = 'SqlSmtp', @recipients = @email_address, @subject = @testmsg, @body = @testmsg
	PRINT '   Sent a test e-mail via the profile [SqlSmtp] to [' + @email_address + ']'
  END

/*  Enable SMTP as the Alert notification mechanism.  */
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', @iValue OUTPUT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', @sValue OUTPUT
IF @iValue <> 1 OR ISNULL(@sValue, '') <> 'SqlSmtp'
  BEGIN
	IF @iValue <> 1
	  BEGIN
		EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
		PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\UseDatabaseMail to 1 from [' + CAST(@iValue as varchar) + ']'
	  END

	IF ISNULL(@sValue, '') <> 'SqlSmtp'
	  BEGIN
		EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SqlSmtp'
		PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\DatabaseMailProfile to [SqlSmtp] from [' + @sValue + ']'
	  END
	PRINT '   Set SMTP to be the Alert Notification Method for the SQLAgent'
  END


/*  Turn off encryption for the master/target job server.  */
SET @bChange = 0
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'AllowDownloadedJobsToMatchProxyName', @iValue OUTPUT
IF @iValue <> 1
  BEGIN
	EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'AllowDownloadedJobsToMatchProxyName', REG_DWORD, 1
	PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\AllowDownloadedJobsToMatchProxyName to 1 from [' + CAST(@iValue as varchar) + ']'
	SET @bChange = 1
  END

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'MsxEncryptChannelOptions', @iValue OUTPUT
IF @iValue <> 0
  BEGIN
	EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'MsxEncryptChannelOptions', REG_DWORD, 0
	PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\MsxEncryptChannelOptions to 0 from [' + CAST(@iValue as varchar) + ']'
	SET @bChange = 1
  END

IF @bChange = 1
	PRINT '   **** NOTE: The SQL Server Agent service needs to be restarted on ' + ISNULL(@@SERVERNAME, 'UNK') + ' for the MSX/TSX change to take affect.'


/* Extend Job History */
SET @bChange = 0
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', @iValue OUTPUT
IF @iValue <> 5000
  BEGIN
	EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', REG_DWORD, 5000
	PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\JobHistoryMaxRows to 5000 from [' + CAST(@iValue as varchar) + ']'
	SET @bChange = 1
  END

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', @iValue OUTPUT
IF @iValue <> 1000
  BEGIN
	EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', REG_DWORD, 1000
	PRINT '   Setting HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent\JobHistoryMaxRowsPerJob to 1000 from [' + CAST(@iValue as varchar) + ']'
	SET @bChange = 1
  END

IF @bChange = 1
	PRINT '   **** NOTE: Set Job History to maintain 5000 rows and 1000 rows per job; a restart of the SQL Agent is required'

SET NOCOUNT OFF
